﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace org.maquech.CEeL.Core.EducationalProgram {
    public sealed class Grade : PersistentObject {
        #region Fields and Properties

        /// <summary>
        /// The Grade's name field.
        /// </summary>
        private string name;

        /// <summary>
        /// The Grade's name.
        /// </summary>
        public string Name {
            get {
                return this.name;
            }
            set {
                if ( this.name != value )
                    modifiedDBFields.Add( "name" );
                this.name = value;
            }
        }

        /// <summary>
        /// The Grade's name abbreviation field.
        /// </summary>
        private string abbreviation;

        /// <summary>
        /// The Grade's name abbreviation.
        /// </summary>
        public string Abbreviation {
            get {
                return this.abbreviation;
            }
            set {
                if ( this.abbreviation != value )
                    modifiedDBFields.Add( "abbreviation" );
                this.abbreviation = value;
            }
        }

        /// <summary>
        /// The Grade's ordering field.
        /// </summary>
        private int ordering;

        /// <summary>
        /// The Grade's ordering.
        /// </summary>
        public int Ordering {
            get {
                return this.ordering;
            }
            set {
                if ( this.ordering != value )
                    modifiedDBFields.Add( "ordering" );
                this.ordering = value;
            }
        }

        /// <summary>
        /// The Grade's numerical value field.
        /// </summary>
        private decimal numericalValue;

        /// <summary>
        /// The Grade's numerical value.
        /// </summary>
        public decimal NumericalValue {
            get {
                return this.numericalValue;
            }
            set {
                if ( this.numericalValue != value )
                    modifiedDBFields.Add( "numerical_value" );
                this.numericalValue = value;
            }
        }

        /// <summary>
        /// The Grade's passing field.
        /// </summary>
        private bool passingGrade;

        /// <summary>
        /// True if <code>this</code> is a passing Grade, false otherwise.
        /// </summary>
        public bool PassingGrade {
            get {
                return this.passingGrade;
            }
            set {
                if ( this.passingGrade != value )
                    modifiedDBFields.Add( "passing_grade" );
                this.passingGrade = value;
            }
        }

        /// <summary>
        /// Loads all the Grades equivalent to <code>this</code>.
        /// </summary>
        /// <returns>A List containing all of the Grades on the database.</returns>
        public List<Grade> Equivalences ( ) {
            List<Grade> gl = new List<Grade>( );
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT id, name, abbreviation, numerical_value, passing_grade, ordering"
                + " FROM grades AS g, grade_equivalences as ge"
                + " WHERE ge.grade_id = @id"
                + " AND ge.equivalent_to_id = g.id"
                + " ORDER BY ordering, name";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32 );
            using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                while ( rdr.Read( ) ) {
                    gl.Add( new Grade( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ], ( decimal ) rdr [ "numerical_value" ],
                            ( bool ) rdr [ "passing_grade" ], ( int ) rdr [ "ordering" ] ) );
                }
                rdr.Close( );
            }
            return gl;
        }

        /// <summary>
        /// Checks if the current instance is valid to be stored in the database.
        /// </summary>
        public override bool IsValid {
            get {
                return !( this.name == null ) && ( this.name.Trim( ) != "" )
                    && ( !( this.abbreviation == null ) && ( this.abbreviation.Trim( ) != "" )
                    && this.ordering >= 0
                    && this.numericalValue >= 0 );
            }
        }
        #endregion

        #region Constructors
        public Grade ( ) {
            this.id = -1;
            this.name = "";
            this.abbreviation = "";
            this.ordering = 0;
            this.numericalValue = 0;
            this.passingGrade = true;
            this.isNew = true;
        }

        public Grade ( int id, string name, string abbreviation, decimal numericalValue, bool passingGrade, int ordering ) {
            this.id = id;
            this.name = name;
            this.abbreviation = abbreviation;
            this.numericalValue = numericalValue;
            this.passingGrade = passingGrade;
            this.ordering = ordering;
            this.isNew = ( id == -1 );
        }

        public Grade ( string name, string abbreviation, decimal numericalValue, bool passingGrade, int ordering )
            : this( -1, name, abbreviation, numericalValue, passingGrade, ordering ) {
        }
        #endregion

        #region Methods

        #region System.Object Methods
        /// <summary>
        /// Checks if the object is equal to the current instance.
        /// </summary>
        /// <param name="obj">The object to check for equality</param>
        /// <returns><code>True</code> if the objects are equal, <code>false</code> otherwise.</returns>
        public override bool Equals ( object obj ) {
            if ( obj == null ) {
                return false;
            }
            Grade g = obj as Grade;
            if ( ( object ) g == null ) {
                return false;
            }
            return ( this.name == g.name )
                && ( this.abbreviation == g.abbreviation )
                && ( this.passingGrade == g.passingGrade )
                && ( this.numericalValue == g.numericalValue )
                && ( this.ordering == g.ordering );
        }

        /// <summary>
        /// Checks if the Grade is equal to the current instance.
        /// </summary>
        /// <param name="a">The Grade to check for equality</param>
        /// <returns><code>True</code> if the objects are equal, <code>false</code> otherwise.</returns>
        public bool Equals ( Grade g ) {
            if ( ( object ) g == null ) {
                return false;
            }
            return ( this.name == g.name )
                && ( this.abbreviation == g.abbreviation )
                && ( this.passingGrade == g.passingGrade )
                && ( this.numericalValue == g.numericalValue )
                && ( this.ordering == g.ordering );
        }

        public override int GetHashCode ( ) {
            return id.GetHashCode( )
                ^ this.name.GetHashCode( )
                ^ this.abbreviation.GetHashCode( )
                ^ this.numericalValue.GetHashCode( )
                ^ this.passingGrade.GetHashCode( )
                ^ this.ordering.GetHashCode( );
        }

        /// <summary>
        /// String representation of the current instance.
        /// </summary>
        /// <returns>A string representation of the current instance.</returns>
        public override string ToString ( ) {
            StringBuilder bldr = new StringBuilder( );
            object [ ] objArr = new object [ ] { this.id, this.abbreviation, this.name, this.numericalValue, this.passingGrade, this.ordering };
            bldr.AppendFormat( "{5}) [{0} :  {1}] : {2} ({3}) : Passing grade: {4:Yes|No}", objArr );
            return bldr.ToString( );
        }
        #endregion

        /// <summary>
        /// Saves the current instance to the database.
        /// </summary>
        /// <returns><code>True</code> if the object was saved to the database, <code>false</code> otherwise.</returns>
        public override bool Save ( ) {
            bool saved = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;

            try {
                sqlCommand = "SELECT count(*) = 0 as exists FROM grades WHERE id = @id OR name ILIKE @name";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                isNew = ( bool ) db.ExecuteScalar( dbCommand );
                if ( isNew ) {
                    sqlCommand = "SELECT nextval('grades_id_seq')";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    this.id = ( int ) db.ExecuteScalar( dbCommand );

                    sqlCommand = "INSERT INTO grades ( id, name, abbreviation, numerical_value, passing_grade, ordering )"
                       + " VALUES ( @id, @name, @abbreviation, @ordering )";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                    db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                    db.AddInParameter( dbCommand, "@abbreviation", DbType.String, this.abbreviation );
                    db.AddInParameter( dbCommand, "@numerical_value", DbType.Decimal, this.numericalValue );
                    db.AddInParameter( dbCommand, "@passing_grade", DbType.Boolean, this.passingGrade );
                    db.AddInParameter( dbCommand, "@ordering", DbType.Int32, this.ordering );
                    saved = db.ExecuteNonQuery( dbCommand ) > 0;
                }
                else if ( this.modifiedDBFields.Count > 0 ) {
                    sqlCommand = "UPDATE grades SET name = @name, abbreviation = @abbreviaton, ordering = @ordering,"
                        + "  numerical_value = @numerical_value, passing_grade = @passing_grade"
                        + " WHERE id = @id";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                    db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                    db.AddInParameter( dbCommand, "@abbreviation", DbType.String, this.abbreviation );
                    db.AddInParameter( dbCommand, "@numerical_value", DbType.Decimal, this.numericalValue );
                    db.AddInParameter( dbCommand, "@passing_grade", DbType.Boolean, this.passingGrade );
                    db.AddInParameter( dbCommand, "@ordering", DbType.Int32, this.ordering );
                    saved = db.ExecuteNonQuery( dbCommand ) > 0;
                }
                else {
                    //No update or insert needed
                    saved = true;
                }
            }
            catch ( Exception e ) {
                saved = false;
                this.errorMessages.Add( "There was an error saving the Grade: " + e.Message );
            }
            return saved;
        }

        /// <summary>
        /// Deletes the current object from the database.
        /// </summary>
        /// <returns><code>True</code> if the object was deleted from the database, <code>false</code> otherwise.</returns>
        public override bool Delete ( ) {
            bool deleted = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;
            try {
                sqlCommand = "DELETE FROM grades WHERE id = @id";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                deleted = db.ExecuteNonQuery( dbCommand ) >= 0;
            }
            catch ( Exception e ) {
                deleted = false;
                this.errorMessages.Add( "There was an error saving the Grade: " + e.Message );
            }
            return deleted;
        }


        #region Load Methods
        /// <summary>
        /// Loads the Grade with the given <code>id</code>.
        /// </summary>
        /// <param name="id">The Grades' id.</param>
        /// <returns>A Grade object with it's information or a null Grade object if the <code>id</code> was not present in the database.</returns>
        public static Grade Load ( int id ) {
            Grade g = null;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT name, abbreviation,  numerical_value, passing_grade, ordering FROM grades WHERE id = @id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
            try {
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    if ( rdr.Read( ) ) {
                        g = new Grade( id, ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ], ( decimal ) rdr [ "numerical_value" ],
                            ( bool ) rdr [ "passing_grade" ], ( int ) rdr [ "ordering" ] );
                        g.modifiedDBFields.Clear( );
                        g.ClearMessages( );
                        g.isNew = false;
                    }
                    rdr.Close( );
                }
            }
            catch ( Exception e ) {
                g.errorMessages.Add( string.Format( "There was an error loading the Grade with ID {0}: {1}", id.ToString( ), e.Message ) );
            }
            return g;
        }

        /// <summary>
        /// Loads the Grade with the given <code>name</code>.
        /// </summary>
        /// <param name="id">The Grades' name.</param>
        /// <returns>A Grade object with it's information or a null Grade object if the <code>name</code> was not present in the database.</returns>
        public static Grade LoadByName ( string name ) {
            Grade g = null;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT id, name, abbreviation, numerical_value, passing_grade, ordering FROM grades WHERE name ILIKE @name";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@name", DbType.String, name );
            try {
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    if ( rdr.Read( ) ) {
                        g = new Grade( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ], ( decimal ) rdr [ "numerical_value" ],
                            ( bool ) rdr [ "passing_grade" ], ( int ) rdr [ "ordering" ] );
                        g.modifiedDBFields.Clear( );
                        g.ClearMessages( );
                        g.isNew = false;
                    }
                    rdr.Close( );
                }
            }
            catch ( Exception e ) {
                g.errorMessages.Add( string.Format( "There was an error loading the Grade {0}: {1}", name, e.Message ) );
            }
            return g;
        }

        /// <summary>
        /// Checks the database for existence of the Grade with the given <code>id</code>.
        /// </summary>
        /// <param name="id">The Grades' id.</param>
        /// <returns><code>True</code> if the grade with the given <code>id</code>exists, <code>false</code> otherwise.</returns>
        public static bool Exists ( int id ) {
            bool exists = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 as exists FROM grades WHERE id = @id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
            exists = ( bool ) db.ExecuteScalar( dbCommand );
            return exists;
        }

        /// <summary>
        /// Checks the database for existence of the Grade with the given <code>name</code>.
        /// </summary>
        /// <param name="name">The Grades' name</param>
        /// <returns><code>True</code> if the grade with the given <code>name</code>exists, <code>false</code> otherwise.</returns>
        public static bool ExistsByName ( string name ) {
            bool exists = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 as exists FROM grades WHERE name ILIKE @name";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@name", DbType.String, name );
            exists = ( bool ) db.ExecuteScalar( dbCommand );
            return exists;
        }

        /// <summary>
        /// Loads all of the Grades in the database to a list.
        /// </summary>
        /// <returns>A List containing all of the Grades on the database.</returns>
        public static List<Grade> LoadAll ( ) {
            List<Grade> gl = new List<Grade>( );
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT id, name, abbreviation, numerical_value, passing_grade, ordering FROM grades ORDER BY ordering, name";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                while ( rdr.Read( ) ) {
                    gl.Add( new Grade( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ], ( decimal ) rdr [ "numerical_value" ],
                            ( bool ) rdr [ "passing_grade" ], ( int ) rdr [ "ordering" ] ) );
                }
                rdr.Close( );
            }
            return gl;
        }
        #endregion

        /// <summary>
        /// Checks for equivalence between a Grade and <code>this</code>.
        /// </summary>
        /// <param name="g">A Grade to check for equivalence.</param>
        /// <returns><code>True</code> if the two Grades are equivalent, <code>false</code> otherwise.</returns>
        public bool AreEquivalent ( Grade g ) {
            bool areEquivalent = false;
            string sqlCommand = "SELECT (count(*) = 2) FROM grade_equivalences"
                + " WHERE (g.grade_id = @id AND g.equivalent_to_id = @gid)"
                + " OR (g.grade_id = @gid AND g.equivalent_to_id = @id)";
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
            db.AddInParameter( dbCommand, "@gid", DbType.Int32, g.Id );
            areEquivalent = ( bool ) db.ExecuteScalar( dbCommand );
            return areEquivalent;
        }

        /// <summary>
        /// Checks for equivalence between two Grades
        /// </summary>
        /// <param name="a">Grade one</param>
        /// <param name="b">Grade two</param>
        /// <returns><code>True</code> if the two Grades are equivalent, <code>false</code> otherwise.</returns>
        public static bool AreEquivalent ( Grade a, Grade b ) {
            bool areEquivalent = false;
            string sqlCommand = "SELECT (count(*) = 2) FROM grade_equivalences"
                + " WHERE (g.grade_id = @id AND g.equivalent_to_id = @gid)"
                + " OR (g.grade_id = @gid AND g.equivalent_to_id = @id)";
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, b.id );
            db.AddInParameter( dbCommand, "@gid", DbType.Int32, a.Id );
            areEquivalent = ( bool ) db.ExecuteScalar( dbCommand );
            return areEquivalent;
        }

        /// <summary>
        /// Saves into the database the equivalence between the two Grades.
        /// </summary>
        /// <param name="a">First grade</param>
        /// <param name="b">Second grade</param>
        /// <returns><code>True</code> if the grade equivalence was saved successfully, <code>false</code> otherwise.</returns>
        public static bool SaveEquivalent ( Grade a, Grade b ) {
            bool saved = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand, dbCommandAux;
            string sqlCommand;
            if ( a.IsValid && b.IsValid ) {
                if ( AreEquivalent( a, b ) ) {
                    saved = true;
                }
                else {
                    try {
                        sqlCommand = "INSERT INTO grade_equivalences ( grade_id, equivalent_to_id)"
                            + " VALUES ( @id, @eid )";
                        dbCommand = db.GetSqlStringCommand( sqlCommand );
                        db.AddInParameter( dbCommand, "@id", DbType.Int32, a.id );
                        db.AddInParameter( dbCommand, "@eid", DbType.Int32, b.id );

                        dbCommandAux = db.GetSqlStringCommand( sqlCommand );
                        db.AddInParameter( dbCommandAux, "@id", DbType.Int32, b.id );
                        db.AddInParameter( dbCommandAux, "@eid", DbType.Int32, a.id );

                        using ( DbConnection connection = db.CreateConnection( ) ) {
                            connection.Open( );
                            DbTransaction transaction = connection.BeginTransaction( );
                            try {
                                db.ExecuteNonQuery( dbCommand, transaction );
                                db.ExecuteNonQuery( dbCommandAux, transaction );
                                transaction.Commit( );
                            }
                            catch {
                                transaction.Rollback( );
                                saved = false;
                            }
                        }
                    }
                    catch {
                        saved = false;
                    }
                }
            }
            return saved;
        }
        #endregion
    }
}
